/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Models.Advertise;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Cyro
 */
public class AdvertiseDAO {

    public int insertAdvertise(Advertise advertise) {
        int result = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("insert into Advertise values(?,?,?,?,?,?,?,?,?)");
            stm.setString(1, advertise.getName());
            stm.setString(2, advertise.getDesignation());
            stm.setString(3, advertise.getAddress());
            stm.setString(4, advertise.getMobile());
            stm.setString(5, advertise.getTelephone());
            stm.setString(6, advertise.getFax());
            stm.setString(7, advertise.getEmail());
            stm.setString(8, advertise.getDescription());
            stm.setBoolean(9, false);
            result = stm.executeUpdate();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public ArrayList<Advertise> getAllAdvertise() {
        ArrayList<Advertise> listAdvertise = new ArrayList<Advertise>();
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("select * from Advertise");
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                Advertise a = new Advertise();
                a.setId(rs.getInt("ID"));
                a.setName(rs.getString("Name"));
                a.setDesignation(rs.getString("Designation"));
                a.setAddress(rs.getString("Address"));
                a.setMobile(rs.getString("Mobile"));
                a.setTelephone(rs.getString("Telephone"));
                a.setFax(rs.getString("Fax"));
                a.setEmail(rs.getString("Email"));
                a.setDescription(rs.getString("Description"));
                a.setStatus(rs.getBoolean("Status"));

                listAdvertise.add(a);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listAdvertise;
    }

    public void deleteAdvertiseByID(int id) {
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("DELETE FROM Advertise WHERE ID = ?");
            stm.setInt(1, id);
            stm.executeUpdate();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int statisticAdvertise() {
        int total = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("select count(*) from Advertise");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getCurrentID() {
        int id = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("select MAX(ID) from Advertise");
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return id;
    }
}
